<?php
namespace  Admin\Controller;
use Think\Controller;
echo '<meta http-equiv=Content-Type content="text/html;charset=utf-8">';
class ExcelOrderInfoController extends IndexController{
    public function orders(){
        /** Error reporting */
        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
        define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
        date_default_timezone_set('Asia/Shanghai');

        /**select sql echo 查询输出**/
        $GoodsUser=D("goods_user");
        $where= $where="goods.uid=user.id AND goods.lid=shop.id";
        $goods_user_info= $GoodsUser->table('tb_goods_user goods,tb_user user,tb_shop_class shop')
            ->where($where)
            ->field("goods.id,goods.uid,goods.name,goods.en_name,goods.phone,goods.addr,goods.garden,goods.age,goods.papers_type,papers,is_order,user.name uid,shop.name lid")
            ->select();
        $result=array();
        foreach($goods_user_info as $key=>$value){
            $result[$key]=$value;
            $result[$key]['is_order']=$value['is_order']==0?"未下订单":"已下订单";
            $result[$key]['papers_type']=$value['papers_type']==0?"身份证":"护照";
            $result[$key]['garden']=$value['garden']==0?"女":"男";
        }
        //引入外部的类
        import("Vendor.phpexcel.PHPExcels");
        import("Vendor.phpexcel.PHPExcel.CachedObjectStorageFactory",APP_PATH,".php");
        import("Vendor.phpexcel.PHPExcel.Settings",APP_PATH,".php");
        import("Vendor.phpexcel.PHPExcel.IOFactory",APP_PATH,".php");

        $Factory= new \PHPExcel_CachedObjectStorageFactory();
        $Settings= new \PHPExcel_Settings();
        $IOF= new \PHPExcel_IOFactory();
        $cacheMethod = $Factory::cache_in_memory_gzip;
        if ($Settings::setCacheStorageMethod($cacheMethod)) {
            echo date('H:i:s') , " 使用 " , $cacheMethod , " 方式下载文件" , EOL;
        } else {
            echo date('H:i:s') , "将会使用" , $cacheMethod , "默认方式下载文件" , EOL;
        }
// Create new PHPExcel object
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setCreator("呱呱试管最初创建")
            ->setLastModifiedBy("呱呱试管最终修改")
            ->setTitle("Office 2007 XLSX 格式的文档")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("格式的文档  Office 2007 XLSX, 使用了PHPExcel.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("文档下载成功");
// Create a first sheet
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "id");
        $objPHPExcel->getActiveSheet()->setCellValue('B1', "会员名");
        $objPHPExcel->getActiveSheet()->setCellValue('C1', "商品类别");
        $objPHPExcel->getActiveSheet()->setCellValue('D1', "真实姓名");
        $objPHPExcel->getActiveSheet()->setCellValue('E1', "英文姓名");
        $objPHPExcel->getActiveSheet()->setCellValue('F1', "电话");
        $objPHPExcel->getActiveSheet()->setCellValue('G1', "地址");
        $objPHPExcel->getActiveSheet()->setCellValue('H1', "性别");
        $objPHPExcel->getActiveSheet()->setCellValue('I1', "证件类型");
        $objPHPExcel->getActiveSheet()->setCellValue('J1', "年龄");
        $objPHPExcel->getActiveSheet()->setCellValue('K1', "证件号码");
        $objPHPExcel->getActiveSheet()->setCellValue('L1', "是否是订单");
// Freeze panes
        $objPHPExcel->getActiveSheet()->freezePane('A4');
// Rows to repeat at top
        $objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
// Add data
        foreach ($result as $key=>$value) {
            $key+=2;
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $key, $value['id'])
                ->setCellValue('B' . $key, $value['uid'])
                ->setCellValue('C' . $key, $value['lid'])
                ->setCellValue('D' . $key, $value['name'])
                ->setCellValue('E' . $key, $value['en_name'])
                ->setCellValue('F' . $key, $value['phone'])
                ->setCellValue('G' . $key, $value['addr'])
                ->setCellValue('H' . $key, $value['garden'])
                ->setCellValue('I' . $key, $value['age'])
                ->setCellValue('J' . $key, $value['papers_type'])
                ->setCellValue('K' . $key, $value['papers'])
                ->setCellValue('L' . $key, $value['is_order'])
                ->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');  //页脚
        }
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
        echo date('H:i:s') , " 写EXCEL2007格式" , EOL;
        $callStartTime = microtime(true);
        $objWriter = $IOF::createWriter($objPHPExcel, 'Excel2007');
        $path="./Public/Admin/public/excel/订单用户信息/".date('ymdHis')."订单用户信息.xlsx";
        $fileName=iconv('UTF-8','GB2312', $path);
        //保存文件
        $objWriter->save($fileName);
        echo date('H:i:s') , " [用户订单表]下载EXCEL2007格式成功 " , EOL;
// Echo memory usage
        echo date('H:i:s') , ' 下载大小为: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
        echo date('H:i:s') , "  EXCEL下载成功" , EOL;
        header("Location: index.php?m=Admin&c=Index&a=index#");

    }
}